from decimal import Decimal
from pymysql.converters import escape_string
from django.db import connection, transaction

from app.my_exception import MyException
from app.service import shop
from libs import time_helper
from libs.db import DB
from libs.time_helper import ts_to_time_str


def get_member_by_id(mem_id, cursor=None, fields=None):
    if fields is None:
        fields = ['id', 'username', 'usdt_value', 'user_level', 'point', 'storage','fast_ballance','is_fastmember']
    db_mem = {}
    try:
        db = DB('table_member', conn=cursor)
        db_mem = db.one(fields, {'id': mem_id})
    except Exception as e:
        print(e)
    return db_mem


def get_member_by_username(username, fields=None):
    if fields is None:
        fields = ['id', 'username', 'usdt_value', 'user_level', 'point', 'storage','fast_ballance','is_fastmember']
    db_mem = {}
    try:
        field_str = '`,`'.join(fields)
        sql = 'select `{}` from table_member where username=%s'.format(field_str)
        with connection.cursor() as cursor:
            cursor.execute(sql, username)
            row = cursor.fetchone()
            if row is not None:
                for i, field in enumerate(fields):
                    db_mem.update({field: row[i]})
    except Exception as e:
        print(e)
    return db_mem


def query_members_by_keyword(keyword: str, page):
    db_mem_arr = []
    try:
        with connection.cursor() as cursor:
            db = DB('table_member', conn=cursor, table_as='m')
            left_join_tables = {
                'mi': {'table': 'table_member_info', 'on': 'm.username=mi.username'}
            }
            left_join_fields = {
                'm': ['id', 'username', 'usdt_value', 'user_level', 'point', 'storage','fast_ballance','is_fastmember'],
                'mi': ['name']
            }
            filter_key = 'username like "%%{}%%"'.format(escape_string(keyword))
            left_join_wheres = {
                'm': {
                    filter_key: None
                }
            }
            db_mem_arr = db.left_join(left_join_tables).order_by('m.usdt_value desc').page(page).find(left_join_fields, left_join_wheres)
    except Exception as e:
        print(e)
    return db_mem_arr


# storage 仓储卡
# usdt_value 余额
def get_member_with_cursor(cursor, member_id):
    member_dict = {'id': 0}
    try:
        sql = 'select id,username,usdt_value,user_level,`storage`,ver,fast_ballance from table_member where id=%s'
        cursor.execute(sql, member_id)
        row = cursor.fetchone()
        if row is not None:
            [db_mem_id, username, usdt_value, user_level, storage, ver,fast_ballance] = row
            member_dict = {
                'id': db_mem_id,
                'username': username,
                'usdt_value': usdt_value,
                'user_level': user_level,
                'storage': storage,
                'ver': ver,
                'fast_ballance':fast_ballance
            }
    except Exception as e:
        print(e)
    return member_dict

# 记录会员资金变动 flag>0 为用户
def record_mem_funds_changed(cursor, balance_data, flag):
    log_id = 0
    if type(balance_data) is not dict:
        return log_id, '无效的资金变更信息'
    err = None
    paymentMethod = balance_data.get('PaymentMethod', 0)
    member_id = balance_data.get('member_id', 0)
    username = balance_data.get('username', '')
    usdt_value = balance_data.get('usdt_value', 0)
    storage = balance_data.get('storage', 0)
    user_level = balance_data.get('user_level', 0)
    pay_amount_dec = balance_data.get('pay_amount', Decimal('0'))
    abs_pay_amount_dec = abs(pay_amount_dec)
    if flag == 'reduce':
        app_order_sign = -1
        pay_amount_dec = abs_pay_amount_dec * Decimal('-1')
    elif flag == 'add':
        app_order_sign = 1
        pay_amount_dec = abs_pay_amount_dec
    else:
        return log_id, '无效的资金变动类型'

    ver = balance_data.get('ver')
    next_ver = ver + 1
    if member_id <= 0:
        err = '无效的会员ID'
        return log_id, err

    if paymentMethod == 2:
        # 仓储卡
        balance_field = 'storage'
        money_type = 9
        sys_type = 101
        storage_before_dec = Decimal(storage)
        storage_after_dec = storage_before_dec + pay_amount_dec
        if flag == 'add':
            remark = '仓储卡退款'
        else:
            remark = '仓储卡收银'
            if storage_after_dec < 0:
                err = '仓储卡不足'
                return log_id, err
        storage_before = str(storage_before_dec.quantize(Decimal('0.00')))
        storage_after = str(storage_after_dec.quantize(Decimal('0.00')))
        before_value = storage_before
        after_value = storage_after
    elif paymentMethod == 3:
        # 余额
        balance_field = 'usdt_value'
        money_type = 1
        sys_type = 102
        balance_before_dec = Decimal(usdt_value)
        balance_after_dec = balance_before_dec + pay_amount_dec
        if flag == 'add':
            remark = '余额退款'
        else:
            remark = '余额收银'
            if balance_after_dec < 0:
                err = '余额不足'
                return log_id, err
        balance_before = str(balance_before_dec.quantize(Decimal('0.00')))
        balance_after = str(balance_after_dec.quantize(Decimal('0.00')))
        before_value = balance_before
        after_value = balance_after
    else:
        err = '无效的支付方式'
        return log_id, err

    update_sql = 'update table_member set {}=%s,ver=%s'.format(balance_field)
    if user_level == 0:
        update_sql += ',user_level=1'
    update_sql += ' where id=%s and ver=%s'
    affected = cursor.execute(update_sql, [after_value, next_ver, member_id, ver])
    if affected != 1:
        err = '更新用户余额失败'
        return log_id, err
    insert_flow_sql = 'insert into table_account_flow (username,before_value,after_value,amount,money_type,sys_type,stime,remark,`sign`,is_admin) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    stime = ts_to_time_str(time_format="%Y-%m-%d %H:%M:%S")
    bindings = [username, before_value, after_value, abs_pay_amount_dec,
                money_type, sys_type, stime, remark, app_order_sign, 0]
    affected = cursor.execute(insert_flow_sql, bindings)
    if affected != 1:
        err = '添加资金流水失败'
    log_id = cursor.lastrowid
    return log_id, err

# 记录会员快餐店账户变动 flag>0 为用户
def record_mem_fast_changed(cursor, balance_data, flag):
    log_id = 0
    if type(balance_data) is not dict:
        return log_id, '无效的资金变更信息'
    err = None
    member_id = balance_data.get('member_id', 0)
    username = balance_data.get('username', '')
    fast_ballance = balance_data.get('fast_ballance', 0)
    user_level = balance_data.get('user_level', 0)
    pay_amount_dec = balance_data.get('pay_amount', Decimal('0'))
    abs_pay_amount_dec = abs(pay_amount_dec)
    if flag == 'reduce':
        flow_type = 2
        pay_amount_dec = abs_pay_amount_dec * Decimal('-1')
    elif flag == 'add':
        flow_type = 3
        pay_amount_dec = abs_pay_amount_dec
    else:
        return log_id, '无效的资金变动类型'

    ver = balance_data.get('ver')
    next_ver = ver + 1
    if member_id <= 0:
        err = '无效的会员ID'
        return log_id, err
    fast_before_dec = Decimal(fast_ballance)
    fast_after_dec = fast_before_dec + pay_amount_dec
    if flag == 'reduce':
        if fast_after_dec < 0:
            err = '快餐店账户余额不足'
            return log_id, err
    fast_before = str(fast_before_dec.quantize(Decimal('0.00')))
    fast_after = str(fast_after_dec.quantize(Decimal('0.00')))
    update_sql = 'update table_member set fast_ballance=%s,ver=%s'
    if user_level == 0:
        update_sql += ',user_level=1'
    update_sql += ' where id=%s'
    affected = cursor.execute(update_sql, [fast_after, next_ver, member_id])
    if affected != 1:
        err = '更新用户快餐店账户余额失败'
        return log_id, err
    
    insert_flow_sql = 'insert into table_fastballance_flow (username,before_value,after_value,amount,type) values (%s,%s,%s,%s,%s)'
    bindings = [username, fast_before, fast_after, abs_pay_amount_dec,flow_type]
    affected = cursor.execute(insert_flow_sql, bindings)
    if affected != 1:
        err = '添加资金流水失败'
    log_id = cursor.lastrowid
    return log_id, err


def add_member(mem_data, shop_id):
    mem_id = 0
    try:
        if type(mem_data) is not dict:
            err = '无效的会员信息'
            return mem_id, err
        username = mem_data.get('username')
        db_shop = shop.get_shop(shop_id, ['id', 'shop_name'])
        if db_shop.get('id', 0) == 0:
            return mem_id, '店铺信息不存在'
        db_shop_name = db_shop.get('shop_name', '')
        fields = []
        placeholders = []
        bindings = []
        for key in mem_data:
            val = mem_data.get(key, '')
            fields.append('`{}`'.format(key))
            placeholders.append('%s')
            bindings.append(val)
        with connection.cursor() as cursor:
            with transaction.atomic():
                field_str = ','.join(fields)
                placeholder_str = ','.join(placeholders)
                sql = 'insert into table_member ({}) values ({})'.format(field_str, placeholder_str)
                affected = cursor.execute(sql, bindings)
                if affected != 1:
                    err = '添加会员失败'
                    return mem_id, err
                mem_id = cursor.lastrowid
                db = DB('table_member_info', conn=cursor)
                _, err = db.insert({
                    'username': username,
                    'phone': '',
                    'building_no': '1',
                    'unit': '1',
                    'level': '1',
                    'house_number': '1',
                    'name': '',
                    'com_id': 0,
                    'com_name': '其它',
                    'shop_id': shop_id,
                    'shop_name': db_shop_name,
                })
                if err is not None:
                    raise MyException(err)
    except MyException as e:
        return mem_id, str(e)
    except Exception as e:
        err = '添加会员异常'
        print(err, e)
        return mem_id, err
    return mem_id, err


def add_member_v2(shop_id, mem_data, new_member_info):
    mem_id = 0
    try:
        with connection.cursor() as cursor:
            db = DB('table_shop_address', conn=cursor)
            db_shop = db.one(['id', 'shop_name'], {'id': shop_id})
            if db_shop.get('id', 0) == 0:
                return mem_id, '店铺信息不存在'
            db_shop_name = db_shop.get('shop_name', '')
            with transaction.atomic():
                db = DB('table_member', conn=cursor)
                mem_id, err = db.insert(mem_data)
                if err is not None:
                    raise MyException(err)
                if mem_id == 0:
                    raise MyException('添加会员失败')
                username = mem_data.get('username')
                dt_str = time_helper.ts_to_time_str(time_format='%Y-%m-%d %H:%M:%S')
                member_info = {
                    'username': username,
                    'shop_id': shop_id,
                    'shop_name': db_shop_name,
                    'phone': '',
                    'building_no': '1',
                    'unit': '1',
                    'level': '1',
                    'house_number': '1',
                    'name': '',
                    'com_id': 0,
                    'com_name': '其它',
                    'created_at': dt_str
                }
                member_info.update(new_member_info)
                db = DB('table_member_info', conn=cursor)
                mem_info_id, err = db.insert(member_info)
                if err is not None:
                    raise MyException(err)
                if mem_info_id == 0:
                    raise MyException('添加会员失败')
    except MyException as e:
        return mem_id, str(e)
    except Exception as e:
        err = '添加会员异常'
        print(err, e)
        return mem_id, err
    return mem_id, err
